package xxdsqljobDB

import org.apache.poi.ss.usermodel.BorderStyle
import org.apache.poi.ss.usermodel.Cell
import org.apache.poi.ss.usermodel.CellStyle

import org.apache.poi.ss.usermodel.CreationHelper
import org.apache.poi.ss.usermodel.DataFormat
import org.apache.poi.ss.usermodel.DateUtil
import org.apache.poi.ss.usermodel.FillPatternType
import org.apache.poi.ss.usermodel.Font
import org.apache.poi.ss.usermodel.HorizontalAlignment
import org.apache.poi.ss.usermodel.IndexedColors
import org.apache.poi.ss.usermodel.Row
import org.apache.poi.ss.usermodel.Sheet
import org.apache.poi.ss.usermodel.VerticalAlignment

import org.apache.poi.xssf.streaming.SXSSFCell
import org.apache.poi.xssf.streaming.SXSSFRow
import org.apache.poi.xssf.streaming.SXSSFSheet
import org.apache.poi.xssf.streaming.SXSSFWorkbook
import org.apache.poi.xssf.usermodel.XSSFCell
import org.apache.poi.xssf.usermodel.XSSFCellStyle



import javax.servlet.ServletOutputStream
import javax.servlet.http.HttpServletRequest
import javax.servlet.http.HttpServletResponse

/**
 * Created by Administrator on 2019-01-18.
 * 主要用于解析Html获取各种属性
 */
class ExcelUtil {


    //每张sheet记录数到100W时，则将数据插入到第二张sheet
    public static final SHEET_SPLIT_COUNT = 1000000

    //数据库提每次提交记录数
    public static final CACHE_SIZE = 5000

    public static createExcel2007(physicalName){
        File file = new File(physicalName)
        try {
            if(!file.exists()){
                file.createNewFile()
            }
        }catch (Exception e){
            e.printStackTrace()
        }
        return file
    }

    public static  SXSSFWorkbook getWorkbook( ){
        SXSSFWorkbook workbook = new SXSSFWorkbook(100)
        //workbook.setCompressTempFiles(true);
        return workbook
    }


    public static SXSSFSheet getSheet(SXSSFWorkbook workbook ,String sheetName){
        SXSSFSheet  sheet = workbook.getSheet(sheetName)
        if(!sheet){
            sheet = workbook.createSheet(sheetName)
        }
        return sheet
    }

    public static fillSheetData(SXSSFSheet sheet ,List<Map> dataList,Map rowParam ){
        println "正在写入sheet:${ sheet.getSheetName() }数据......."
        int count = 0 //计数器
        int startIndex = rowParam.get("startIndex")
        CellStyle headerStyle =  rowParam.get("headerStyle") // ExcelUtil.createHeaderStyle(workbook)
        CellStyle bodyStyle =  rowParam.get("bodyStyle")//ExcelUtil.createBodyStyle(workbook)
        //CellStyle footStyle = ExcelUtil.createFootStyle(workbook)
        CreationHelper cellFormat = rowParam.get("cellFormat")
        def headList = []
        if(dataList){
            headList = dataList.get(0).keySet().toList()
        }

        if(startIndex <=0 ){//判定是否有该row
            SXSSFRow header = sheet.createRow(0)
            for (int colIndex = 0; colIndex < headList.size();colIndex ++){
                SXSSFCell cell =  header.createCell(colIndex)
                cell.setCellValue( headList.get(colIndex) )
                cell.setCellStyle(headerStyle)
            }
            ExcelUtil.setSheetProperty(sheet)
        }

        //println "rowIndex:${rowIndex} ${sheet.getLastRowNum()}"
        int rowIndex = startIndex + count
        for(Map data:dataList){
            ++count //记录数据长度 rowIndex = startIndex + count
            rowIndex = startIndex + count//接着上一次行数写入
            //println "正在写入 sheet:${sheet.getSheetName()} 第${rowIndex}行数据......."
            if(rowIndex>=1000001){
                break;//如果SQL记录大于100W，则停止
            }
            SXSSFRow row = sheet.createRow(rowIndex)
            for (int colIndex = 0; colIndex < headList.size();colIndex ++){
                def value = data.get(headList.get(colIndex))
                SXSSFCell cell =  row.createCell(colIndex)
                if(value){
                   cell.setCellValue(value)
                }
                if(value instanceof java.util.Date){
                    CellStyle newStyle = bodyStyle.clone()
                    DataFormat  format = cellFormat.createDataFormat()
                    newStyle.setDataFormat( format.getFormat("yyyy-MM-dd hh:mm:ss"))
                    cell.setCellStyle(newStyle)
                }else{
                    cell.setCellStyle(bodyStyle)
                }
            }
        }
        rowParam.put("startIndex",rowIndex)
    }

    static writeFile(SXSSFWorkbook workbook,File file){
        try {
            //ExcelUtil.fillWorkBookSheetFoot(sheet,footStyle)
            println "正在写入文件:${file.getAbsoluteFile()}....."
            FileOutputStream excelOutput = new FileOutputStream(file) //写入本地文件
            //def excelOutput = response.getOutputStream() //写入输出流
            workbook.write(excelOutput)
            excelOutput.close()
            workbook.dispose()
            println "写入文件完成:${file.getAbsoluteFile()}....."
        }catch (Exception e){
            throw new Exception("写入文件:${file.getAbsoluteFile()}失败:${e.getMessage()}")
        }
    }

    /**
     * 设置列宽
     * @param sheet
     */
    public static setSheetProperty(SXSSFSheet sheet){

        int colCount = sheet.getRow(0)?sheet.getRow(0).getPhysicalNumberOfCells():0
        //设置列宽
        for(int i=0;i<colCount;i++){
            sheet.setColumnWidth(i,(int)sheet.getColumnWidth(i) * 50 / 20)
        }
        //设置sheet只读
        //sheet.protectSheet("")
    }

    /**
     *
     * @param response
     */
    public static void DownLoadExcel2007(HttpServletResponse response,String fileName,Object tempFile){
        response.reset();
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
        response.setHeader("Content-Disposition", String.valueOf("attachment;filename=${fileName}"));
        File file
        if(tempFile instanceof String){
            file = new File(tempFile)
        }else{
            file = tempFile
        }

        //response.setContentLength(file.size())
        FileInputStream fileInputStream = new FileInputStream(file)
        BufferedInputStream bufferedInputStream = new BufferedInputStream(fileInputStream)
        byte[] b = new byte[2048];
        int len = bufferedInputStream.read(b)
        def out = response.getOutputStream()
        while (len>0){
            out.write(b,0,len)
            len = bufferedInputStream.read(b)
        }
        out.flush()
        out.close()
        bufferedInputStream.close()
        fileInputStream.close();
    }

    /**
     * 创建表格页脚行
     * @param sheet
     */
    static void fillWorkBookSheetFoot( Sheet sheet, CellStyle style){
        int rowCount = sheet.getPhysicalNumberOfRows()
        int colCount = sheet.getRow(0).getPhysicalNumberOfCells()
        Cell cell  ,cell1

        if(colCount < 4){
            Row row = sheet.createRow(rowCount+2)
            cell = row.createCell(0)
            cell.setCellValue("制表人:")
            Row row1 = sheet.createRow(rowCount+3)
            cell1 = row1.createCell(0)
            cell1.setCellValue("制表日期:")
        }else{
            Row row = sheet.createRow(rowCount+2)
            cell = row.createCell(colCount-4)
            cell.setCellValue("制表人:")
            cell1 = row.createCell(colCount-2)
            cell1.setCellValue("制表日期:")
        }


        cell.setCellStyle(style)
        cell1.setCellStyle(style)
    }


    /**
     * 创建Excel-header样式
     * @param wb
     * @return
     */
    static createHeaderStyle( SXSSFWorkbook wb){
        CellStyle  xssfCellStyle  = wb.createCellStyle()
        //设置居中
        xssfCellStyle.setAlignment(HorizontalAlignment.CENTER)
        xssfCellStyle.setVerticalAlignment(VerticalAlignment.CENTER)
        //设置边框
        xssfCellStyle.setBorderBottom(BorderStyle.THIN)
        xssfCellStyle.setBorderLeft(BorderStyle.THIN)
        xssfCellStyle.setBorderRight(BorderStyle.THIN)
        xssfCellStyle.setBorderTop(BorderStyle.THIN)
        xssfCellStyle.setLeftBorderColor(IndexedColors.GREY_40_PERCENT.index)
        xssfCellStyle.setRightBorderColor(IndexedColors.GREY_40_PERCENT.index)
        xssfCellStyle.setBottomBorderColor(IndexedColors.GREY_40_PERCENT.index)
        xssfCellStyle.setTopBorderColor(IndexedColors.GREY_40_PERCENT.index)
        //设置背景
        xssfCellStyle.setFillForegroundColor( IndexedColors.GREY_25_PERCENT.index);// 设置背景色
        xssfCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        //自动换行
        xssfCellStyle.setWrapText(true)
        //设置字体
        xssfCellStyle.setFont(createHeaderFont(wb))
        return xssfCellStyle
    }


    /**
     * 创建Excel-body样式
     * @param wb
     * @return
     */
    static  CellStyle createBodyStyle( SXSSFWorkbook wb){
        CellStyle  xssfCellStyle  = wb.createCellStyle()
        //设置居中
        xssfCellStyle.setAlignment(HorizontalAlignment.CENTER)
        xssfCellStyle.setVerticalAlignment(VerticalAlignment.CENTER)
        //设置边框
        xssfCellStyle.setBorderBottom(BorderStyle.THIN)
        xssfCellStyle.setBorderLeft(BorderStyle.THIN)
        xssfCellStyle.setBorderRight(BorderStyle.THIN)
        xssfCellStyle.setBorderTop(BorderStyle.THIN)
        xssfCellStyle.setLeftBorderColor(IndexedColors.GREY_40_PERCENT.index)
        xssfCellStyle.setRightBorderColor(IndexedColors.GREY_40_PERCENT.index)
        xssfCellStyle.setBottomBorderColor(IndexedColors.GREY_40_PERCENT.index)
        xssfCellStyle.setTopBorderColor(IndexedColors.GREY_40_PERCENT.index)
        //设置字体
        xssfCellStyle.setFont(createBodyFont(wb))
        return xssfCellStyle
    }

    /**
     * 创建Excel-body样式
     * @param wb
     * @return
     */
    static XSSFCellStyle createFootStyle( SXSSFWorkbook wb){
        CellStyle  xssfCellStyle  = wb.createCellStyle()
        //设置居中
        xssfCellStyle.setAlignment(HorizontalAlignment.CENTER)
        xssfCellStyle.setVerticalAlignment(VerticalAlignment.CENTER)
        //设置字体
        xssfCellStyle.setFont(createHeaderFont(wb))
        return xssfCellStyle
    }

    static createHeaderFont( SXSSFWorkbook wb){
        Font font = wb.createFont()
        font.setBold(true)
        font.setFontHeightInPoints((short)10)
        font.setFontName("宋体");
        return font
    }

    static createBodyFont( SXSSFWorkbook wb){
        Font font = wb.createFont()
        font.setBold(false)
        font.setFontHeightInPoints((short)10)
        font.setFontName("宋体");
        return font
    }


    static String EncodingFileName(HttpServletRequest request ,String fileName){
        String newFile = ""
        String userAgent = request.getHeader("user-agent").toLowerCase();
        if (userAgent.contains("msie") || userAgent.contains("like gecko") ) {
            // win10 ie edge 浏览器 和其他系统的ie
            newFile = URLEncoder.encode(fileName, "UTF-8");
        } else {
            // fe
            newFile = new String(fileName.getBytes("utf-8"), "iso-8859-1");
        }
        return newFile
    }

}
